using System;
using System.Data;
using System.Data.Common;
using System.Configuration;
using System.Data.SqlClient;

using SIS.ENT;

/// <summary>
/// The EmployeeProfileTableDB class is responsible for interacting with the database to retrieve and store information
/// about EmployeeProfileTable objects.
/// </summary>
public class EmployeeProfileTableDB

{
#region "Public Methods"

/// <summary>
/// Gets an instance of EmployeeProfileTable from the underlying datasource.
/// </summary>
/// <param name="employeeProfileKey">The unique EmployeeProfileKey of the EmployeeProfileTable in the database.</param>
/// <returns>An EmployeeProfileTable when the EmployeeProfileKey was found in the database, or null otherwise.</returns>
public static EmployeeProfileTable GetItem(int employeeProfileKey)
{
EmployeeProfileTable myEmployeeProfileTable = null;
using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
{
using (SqlCommand myCommand = new SqlCommand("sprocEmployeeProfileTableSelectSingleItem", myConnection))
{
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.AddWithValue("@employeeProfileKey", employeeProfileKey);

myConnection.Open();
using (SqlDataReader myReader = myCommand.ExecuteReader())
{
if (myReader.Read()) {
myEmployeeProfileTable = FillDataRecord(myReader);
}
myReader.Close();
}
myConnection.Close();
}
return myEmployeeProfileTable;
}
}


public static EmployeeProfileTable GetItem(int employeeProfileKey,int SchoolKey)
{
    EmployeeProfileTable myEmployeeProfileTable = null;
    using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
    {
        using (SqlCommand myCommand = new SqlCommand("sprocEmployeeProfileTableSelectSingleItem", myConnection))
        {
            myCommand.CommandType = CommandType.StoredProcedure;
            myCommand.Parameters.AddWithValue("@employeeProfileKey", employeeProfileKey);
            myCommand.Parameters.AddWithValue("@SchoolKey", SchoolKey);
            myConnection.Open();
            using (SqlDataReader myReader = myCommand.ExecuteReader())
            {
                if (myReader.Read())
                {
                    myEmployeeProfileTable = FillDataRecord(myReader);
                }
                myReader.Close();
            }
            myConnection.Close();
        }
        return myEmployeeProfileTable;
    }
}

public static EmployeeProfileTable GetItemByUserAccountKey(int UserAccountKey, int SchoolKey)
{
    EmployeeProfileTable myEmployeeProfileTable = null;
    using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
    {
        using (SqlCommand myCommand = new SqlCommand("sprocEmployeeProfileTableSelectSingleItemByUserAccountKey", myConnection))
        {
            myCommand.CommandType = CommandType.StoredProcedure;
            myCommand.Parameters.AddWithValue("@UserAccountKey", UserAccountKey);
            myCommand.Parameters.AddWithValue("@SchoolKey", SchoolKey);
            myConnection.Open();
            using (SqlDataReader myReader = myCommand.ExecuteReader())
            {
                if (myReader.Read())
                {
                    myEmployeeProfileTable = FillDataRecord(myReader);
                }
                myReader.Close();
            }
            myConnection.Close();
        }
        return myEmployeeProfileTable;
    }
}

/// <summary>
/// Returns a list with EmployeeProfileTable objects.
/// </summary>
/// <returns>A generics List with the EmployeeProfileTable objects.</returns>
public static EmployeeProfileTableList GetList()
{
EmployeeProfileTableList tempList = new EmployeeProfileTableList();
using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
{
using (SqlCommand myCommand = new SqlCommand("sprocEmployeeProfileTableSelectList", myConnection))
{
myCommand.CommandType = CommandType.StoredProcedure;

myConnection.Open();
using (SqlDataReader myReader = myCommand.ExecuteReader())
{
if (myReader.HasRows)
{
while (myReader.Read())
{
tempList.Add(FillDataRecord(myReader));
}
}
myReader.Close();
}
}
}
return tempList;
}

/// <summary>
/// Returns a list with EmployeeProfileTable objects.
/// </summary>
/// <returns>A generics List with the EmployeeProfileTable objects.</returns>
public static EmployeeProfileTableList GetListByBloodGroupCode(short BloodGroupCode)
{
EmployeeProfileTableList tempList = new EmployeeProfileTableList();
using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
{
using (SqlCommand myCommand = new SqlCommand("sprocEmployeeProfileTableSelectListByBloodGroupCode", myConnection))
{
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.AddWithValue("@bloodGroupCode", BloodGroupCode);
myConnection.Open();
using (SqlDataReader myReader = myCommand.ExecuteReader())
{
if (myReader.HasRows)
{
while (myReader.Read())
{
tempList.Add(FillDataRecord(myReader));
}
}
myReader.Close();
}
}
return tempList;
}
}

/// <summary>
/// Returns a list with EmployeeProfileTable objects.
/// </summary>
/// <returns>A generics List with the EmployeeProfileTable objects.</returns>
public static EmployeeProfileTableList GetListByMartialStatusCode(short MartialStatusCode)
{
EmployeeProfileTableList tempList = new EmployeeProfileTableList();
using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
{
using (SqlCommand myCommand = new SqlCommand("sprocEmployeeProfileTableSelectListByMartialStatusCode", myConnection))
{
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.AddWithValue("@martialStatusCode", MartialStatusCode);
myConnection.Open();
using (SqlDataReader myReader = myCommand.ExecuteReader())
{
if (myReader.HasRows)
{
while (myReader.Read())
{
tempList.Add(FillDataRecord(myReader));
}
}
myReader.Close();
}
}
return tempList;
}
}

/// <summary>
/// Returns a list with EmployeeProfileTable objects.
/// </summary>
/// <returns>A generics List with the EmployeeProfileTable objects.</returns>
public static EmployeeProfileTableList GetListByNationalityCode(short NationalityCode)
{
EmployeeProfileTableList tempList = new EmployeeProfileTableList();
using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
{
using (SqlCommand myCommand = new SqlCommand("sprocEmployeeProfileTableSelectListByNationalityCode", myConnection))
{
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.AddWithValue("@nationalityCode", NationalityCode);
myConnection.Open();
using (SqlDataReader myReader = myCommand.ExecuteReader())
{
if (myReader.HasRows)
{
while (myReader.Read())
{
tempList.Add(FillDataRecord(myReader));
}
}
myReader.Close();
}
}
return tempList;
}
}

/// <summary>
/// Returns a list with EmployeeProfileTable objects.
/// </summary>
/// <returns>A generics List with the EmployeeProfileTable objects.</returns>
public static EmployeeProfileTableList GetListByRaceCode(short RaceCode)
{
EmployeeProfileTableList tempList = new EmployeeProfileTableList();
using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
{
using (SqlCommand myCommand = new SqlCommand("sprocEmployeeProfileTableSelectListByRaceCode", myConnection))
{
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.AddWithValue("@raceCode", RaceCode);
myConnection.Open();
using (SqlDataReader myReader = myCommand.ExecuteReader())
{
if (myReader.HasRows)
{
while (myReader.Read())
{
tempList.Add(FillDataRecord(myReader));
}
}
myReader.Close();
}
}
return tempList;
}
}

/// <summary>
/// Returns a list with EmployeeProfileTable objects.
/// </summary>
/// <returns>A generics List with the EmployeeProfileTable objects.</returns>
public static EmployeeProfileTableList GetListByReligionCode(short ReligionCode)
{
EmployeeProfileTableList tempList = new EmployeeProfileTableList();
using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
{
using (SqlCommand myCommand = new SqlCommand("sprocEmployeeProfileTableSelectListByReligionCode", myConnection))
{
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.AddWithValue("@religionCode", ReligionCode);
myConnection.Open();
using (SqlDataReader myReader = myCommand.ExecuteReader())
{
if (myReader.HasRows)
{
while (myReader.Read())
{
tempList.Add(FillDataRecord(myReader));
}
}
myReader.Close();
}
}
return tempList;
}
}

/// <summary>
/// Returns a list with EmployeeProfileTable objects.
/// </summary>
/// <returns>A generics List with the EmployeeProfileTable objects.</returns>
public static EmployeeProfileTableList GetListByUserAccountKey(int UserAccountKey)
{
EmployeeProfileTableList tempList = new EmployeeProfileTableList();
using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
{
using (SqlCommand myCommand = new SqlCommand("sprocEmployeeProfileTableSelectListByUserAccountKey", myConnection))
{
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.AddWithValue("@userAccountKey", UserAccountKey);
myConnection.Open();
using (SqlDataReader myReader = myCommand.ExecuteReader())
{
if (myReader.HasRows)
{
while (myReader.Read())
{
tempList.Add(FillDataRecord(myReader));
}
}
myReader.Close();
}
}
return tempList;
}
}

/// <summary>
/// Saves a EmployeeProfileTable in the database.
/// </summary>
/// <param name="myEmployeeProfileTable">The EmployeeProfileTable instance to save.</param>
/// <returns>The new EmployeeProfileKey if the EmployeeProfileTable is new in the database or the existing EmployeeProfileKey when an item was updated.</returns>
public static int Save(EmployeeProfileTable myEmployeeProfileTable)
{
int result = 0;
using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
{
using (SqlCommand myCommand = new SqlCommand("sprocEmployeeProfileTableInsertUpdateSingleItem", myConnection))
{
myCommand.CommandType = CommandType.StoredProcedure;

if (myEmployeeProfileTable.EmployeeProfileKey == -1)
{myCommand.Parameters.AddWithValue("@employeeProfileKey", DBNull.Value);
}
else
{
myCommand.Parameters.AddWithValue("@employeeProfileKey", myEmployeeProfileTable.EmployeeProfileKey);
}
if (myEmployeeProfileTable.UserAccountKey == null){
myCommand.Parameters.AddWithValue("@userAccountKey", DBNull.Value);
}
else
{
myCommand.Parameters.AddWithValue("@userAccountKey", myEmployeeProfileTable.UserAccountKey);
}
if (myEmployeeProfileTable.FirstName == null){
myCommand.Parameters.AddWithValue("@firstName", DBNull.Value);
}
else
{
myCommand.Parameters.AddWithValue("@firstName", myEmployeeProfileTable.FirstName);
}
if (myEmployeeProfileTable.LastName == null){
myCommand.Parameters.AddWithValue("@lastName", DBNull.Value);
}
else
{
myCommand.Parameters.AddWithValue("@lastName", myEmployeeProfileTable.LastName);
}
if (myEmployeeProfileTable.OtherName == null){
myCommand.Parameters.AddWithValue("@otherName", DBNull.Value);
}
else
{
myCommand.Parameters.AddWithValue("@otherName", myEmployeeProfileTable.OtherName);
}
 
if (myEmployeeProfileTable.DateOfBirth == null){
myCommand.Parameters.AddWithValue("@dateOfBirth", DBNull.Value);
}
else
{
myCommand.Parameters.AddWithValue("@dateOfBirth", myEmployeeProfileTable.DateOfBirth);
}
if (myEmployeeProfileTable.PlaceOfBirth == null){
myCommand.Parameters.AddWithValue("@placeOfBirth", DBNull.Value);
}
else
{
myCommand.Parameters.AddWithValue("@placeOfBirth", myEmployeeProfileTable.PlaceOfBirth);
}
if (myEmployeeProfileTable.JoinDate == null){
myCommand.Parameters.AddWithValue("@joinDate", DBNull.Value);
}
else
{
myCommand.Parameters.AddWithValue("@joinDate", myEmployeeProfileTable.JoinDate);
}
if (myEmployeeProfileTable.ParentStatus == null){
myCommand.Parameters.AddWithValue("@parentStatus", DBNull.Value);
}
else
{
myCommand.Parameters.AddWithValue("@parentStatus", myEmployeeProfileTable.ParentStatus);
}
if (myEmployeeProfileTable.Hobby == null){
myCommand.Parameters.AddWithValue("@hobby", DBNull.Value);
}
else
{
myCommand.Parameters.AddWithValue("@hobby", myEmployeeProfileTable.Hobby);
}
if (myEmployeeProfileTable.BloodGroupCode == null){
myCommand.Parameters.AddWithValue("@bloodGroupCode", DBNull.Value);
}
else
{
myCommand.Parameters.AddWithValue("@bloodGroupCode", myEmployeeProfileTable.BloodGroupCode);
}
if (myEmployeeProfileTable.MartialStatusCode == null){
myCommand.Parameters.AddWithValue("@martialStatusCode", DBNull.Value);
}
else
{
myCommand.Parameters.AddWithValue("@martialStatusCode", myEmployeeProfileTable.MartialStatusCode);
}
if (myEmployeeProfileTable.RaceCode == null){
myCommand.Parameters.AddWithValue("@raceCode", DBNull.Value);
}
else
{
myCommand.Parameters.AddWithValue("@raceCode", myEmployeeProfileTable.RaceCode);
}
if (myEmployeeProfileTable.ReligionCode == null){
myCommand.Parameters.AddWithValue("@religionCode", DBNull.Value);
}
else
{
myCommand.Parameters.AddWithValue("@religionCode", myEmployeeProfileTable.ReligionCode);
}
if (myEmployeeProfileTable.NationalityCode == null){
myCommand.Parameters.AddWithValue("@nationalityCode", DBNull.Value);
}
else
{
myCommand.Parameters.AddWithValue("@nationalityCode", myEmployeeProfileTable.NationalityCode);
}
if (myEmployeeProfileTable.NRICNumber == null){
myCommand.Parameters.AddWithValue("@nRICNumber", DBNull.Value);
}
else
{
myCommand.Parameters.AddWithValue("@nRICNumber", myEmployeeProfileTable.NRICNumber);
}
if (myEmployeeProfileTable.DrivingLicenceType == null){
myCommand.Parameters.AddWithValue("@drivingLicenceType", DBNull.Value);
}
else
{
myCommand.Parameters.AddWithValue("@drivingLicenceType", myEmployeeProfileTable.DrivingLicenceType);
}
if (myEmployeeProfileTable.DrivingLicenseNumber == null){
myCommand.Parameters.AddWithValue("@drivingLicenseNumber", DBNull.Value);
}
else
{
myCommand.Parameters.AddWithValue("@drivingLicenseNumber", myEmployeeProfileTable.DrivingLicenseNumber);
}
if (myEmployeeProfileTable.TravelDocumentType == null){
myCommand.Parameters.AddWithValue("@travelDocumentType", DBNull.Value);
}
else
{
myCommand.Parameters.AddWithValue("@travelDocumentType", myEmployeeProfileTable.TravelDocumentType);
}
if (myEmployeeProfileTable.TravelDocumentNumber == null){
myCommand.Parameters.AddWithValue("@travelDocumentNumber", DBNull.Value);
}
else
{
myCommand.Parameters.AddWithValue("@travelDocumentNumber", myEmployeeProfileTable.TravelDocumentNumber);
}
if (myEmployeeProfileTable.WebSiteUrl == null){
myCommand.Parameters.AddWithValue("@webSiteUrl", DBNull.Value);
}
else
{
myCommand.Parameters.AddWithValue("@webSiteUrl", myEmployeeProfileTable.WebSiteUrl);
}
if (myEmployeeProfileTable.Status == null){
myCommand.Parameters.AddWithValue("@status", DBNull.Value);
}
else
{
myCommand.Parameters.AddWithValue("@status", myEmployeeProfileTable.Status);
}
if (myEmployeeProfileTable.CreatedDateTime == null){
myCommand.Parameters.AddWithValue("@createdDateTime", DBNull.Value);
}
else
{
myCommand.Parameters.AddWithValue("@createdDateTime", myEmployeeProfileTable.CreatedDateTime);
}
if (myEmployeeProfileTable.ModifiedDateTime == null){
myCommand.Parameters.AddWithValue("@modifiedDateTime", DBNull.Value);
}
else
{
myCommand.Parameters.AddWithValue("@modifiedDateTime", myEmployeeProfileTable.ModifiedDateTime);
}
 
if (myEmployeeProfileTable.Gender == null){
myCommand.Parameters.AddWithValue("@gender", DBNull.Value);
}
else
{
myCommand.Parameters.AddWithValue("@gender", myEmployeeProfileTable.Gender);
}

DbParameter returnValue;
returnValue = myCommand.CreateParameter();
returnValue.Direction = ParameterDirection.ReturnValue;
myCommand.Parameters.Add(returnValue);

myConnection.Open();
myCommand.ExecuteNonQuery();
result = Convert.ToInt32(returnValue.Value);
myConnection.Close();
}
}
return result;
}

/// <summary>
/// Deletes a EmployeeProfileTable from the database.
/// </summary>
/// <param name="employeeProfileKey">The EmployeeProfileKey of the EmployeeProfileTable to delete.</param>
/// <returns>Returns true when the object was deleted successfully, or false otherwise.</returns>
public static bool Delete(int employeeProfileKey)
{
int result = 0;
using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
{
using (SqlCommand myCommand = new SqlCommand("sprocEmployeeProfileTableDeleteSingleItem", myConnection))
{
myCommand.CommandType = CommandType.StoredProcedure;

myCommand.Parameters.AddWithValue("@employeeProfileKey", employeeProfileKey);
myConnection.Open();
result = myCommand.ExecuteNonQuery();
myConnection.Close();
}
}
return result > 0;
}

#endregion

/// <summary>
/// Initializes a new instance of the EmployeeProfileTable class and fills it with the data fom the IDataRecord.
/// </summary>
private static EmployeeProfileTable FillDataRecord(IDataRecord myDataRecord )
{
EmployeeProfileTable myEmployeeProfileTable = new EmployeeProfileTable();
if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("EmployeeProfileKey")))
{
myEmployeeProfileTable.EmployeeProfileKey = myDataRecord.GetInt32(myDataRecord.GetOrdinal("EmployeeProfileKey"));
}
if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("UserAccountKey")))
{
myEmployeeProfileTable.UserAccountKey = myDataRecord.GetInt32(myDataRecord.GetOrdinal("UserAccountKey"));
}
if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("FirstName")))
{
myEmployeeProfileTable.FirstName = myDataRecord.GetString(myDataRecord.GetOrdinal("FirstName"));
}
if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("LastName")))
{
myEmployeeProfileTable.LastName = myDataRecord.GetString(myDataRecord.GetOrdinal("LastName"));
}
if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("OtherName")))
{
myEmployeeProfileTable.OtherName = myDataRecord.GetString(myDataRecord.GetOrdinal("OtherName"));
}
 if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("Photo")))
{
    myEmployeeProfileTable.Photo = (byte[])myDataRecord.GetValue(myDataRecord.GetOrdinal("Photo"));
}
if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("DateOfBirth")))
{
myEmployeeProfileTable.DateOfBirth = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("DateOfBirth"));
}
if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("PlaceOfBirth")))
{
myEmployeeProfileTable.PlaceOfBirth = myDataRecord.GetString(myDataRecord.GetOrdinal("PlaceOfBirth"));
}
if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("JoinDate")))
{
myEmployeeProfileTable.JoinDate = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("JoinDate"));
}
if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("ParentStatus")))
{
myEmployeeProfileTable.ParentStatus = myDataRecord.GetInt16(myDataRecord.GetOrdinal("ParentStatus"));
}
if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("Hobby")))
{
myEmployeeProfileTable.Hobby = myDataRecord.GetString(myDataRecord.GetOrdinal("Hobby"));
}
if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("BloodGroupCode")))
{
myEmployeeProfileTable.BloodGroupCode = myDataRecord.GetInt16(myDataRecord.GetOrdinal("BloodGroupCode"));
}
if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("MartialStatusCode")))
{
myEmployeeProfileTable.MartialStatusCode = myDataRecord.GetInt16(myDataRecord.GetOrdinal("MartialStatusCode"));
}
if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("RaceCode")))
{
myEmployeeProfileTable.RaceCode = myDataRecord.GetInt16(myDataRecord.GetOrdinal("RaceCode"));
}
if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("ReligionCode")))
{
myEmployeeProfileTable.ReligionCode = myDataRecord.GetInt16(myDataRecord.GetOrdinal("ReligionCode"));
}
if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("NationalityCode")))
{
myEmployeeProfileTable.NationalityCode = myDataRecord.GetInt16(myDataRecord.GetOrdinal("NationalityCode"));
}
if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("NRICNumber")))
{
myEmployeeProfileTable.NRICNumber = myDataRecord.GetString(myDataRecord.GetOrdinal("NRICNumber"));
}
if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("DrivingLicenceType")))
{
myEmployeeProfileTable.DrivingLicenceType = myDataRecord.GetInt16(myDataRecord.GetOrdinal("DrivingLicenceType"));
}
if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("DrivingLicenseNumber")))
{
myEmployeeProfileTable.DrivingLicenseNumber = myDataRecord.GetString(myDataRecord.GetOrdinal("DrivingLicenseNumber"));
}
if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("TravelDocumentType")))
{
myEmployeeProfileTable.TravelDocumentType = myDataRecord.GetInt16(myDataRecord.GetOrdinal("TravelDocumentType"));
}
if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("TravelDocumentNumber")))
{
myEmployeeProfileTable.TravelDocumentNumber = myDataRecord.GetString(myDataRecord.GetOrdinal("TravelDocumentNumber"));
}
if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("WebSiteUrl")))
{
myEmployeeProfileTable.WebSiteUrl = myDataRecord.GetString(myDataRecord.GetOrdinal("WebSiteUrl"));
}
if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("Status")))
{
myEmployeeProfileTable.Status = myDataRecord.GetInt16(myDataRecord.GetOrdinal("Status"));
}
if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("CreatedDateTime")))
{
myEmployeeProfileTable.CreatedDateTime = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("CreatedDateTime"));
}
if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("ModifiedDateTime")))
{
myEmployeeProfileTable.ModifiedDateTime = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("ModifiedDateTime"));
} 
if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("Gender")))
{
myEmployeeProfileTable.Gender = myDataRecord.GetString(myDataRecord.GetOrdinal("Gender"));
}
if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("Employee")))
{
    myEmployeeProfileTable.Employee = myDataRecord.GetString(myDataRecord.GetOrdinal("Employee"));
}
if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("SchoolKey")))
{
    myEmployeeProfileTable.SchoolKey = myDataRecord.GetInt32(myDataRecord.GetOrdinal("SchoolKey"));
}
return myEmployeeProfileTable;
}

public static EmployeeProfileTableList GetList(int SchoolKey)
{
    EmployeeProfileTableList tempList = new EmployeeProfileTableList();
    using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
    {
        using (SqlCommand myCommand = new SqlCommand("sprocEmployeeProfileTableSelectList", myConnection))
        {
            myCommand.CommandType = CommandType.StoredProcedure;
            myCommand.Parameters.AddWithValue("@SchoolKey", SchoolKey);
            myConnection.Open();
            using (SqlDataReader myReader = myCommand.ExecuteReader())
            {
                if (myReader.HasRows)
                {
                    while (myReader.Read())
                    {
                        tempList.Add(FillDataRecord(myReader));
                    }
                }
                myReader.Close();
            }
        }
    }
    return tempList;
}

public static EmployeeProfileTableList GetList(int SchoolKey, string Name, string Address, string NRICNO, short AddressType, short CountryCode, int CityCode, int DistrictCode)
{
    EmployeeProfileTableList tempList = new EmployeeProfileTableList();
    using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
    {
        using (SqlCommand myCommand = new SqlCommand("sprocEmployeeProfileTableSelectListBySearch", myConnection))
        {
            myCommand.CommandType = CommandType.StoredProcedure;
            myCommand.Parameters.AddWithValue("@schoolKey", SchoolKey);
            myCommand.Parameters.AddWithValue("@NRICNumber", NRICNO);
            myCommand.Parameters.AddWithValue("@Name", Name);
            myCommand.Parameters.AddWithValue("@Address", Address);
            myCommand.Parameters.AddWithValue("@AddressTypeCode", AddressType);
            myCommand.Parameters.AddWithValue("@CountryCode", CountryCode);
            myCommand.Parameters.AddWithValue("@CityCode", CityCode);
            myCommand.Parameters.AddWithValue("@DistrictCode", DistrictCode);

            myConnection.Open();
            using (SqlDataReader myReader = myCommand.ExecuteReader())
            {
                if (myReader.HasRows)
                {
                    while (myReader.Read())
                    {
                        tempList.Add(FillDataSearchRecord(myReader));
                    }
                }
                myReader.Close();
            }
        }
    }
    return tempList;
}

private static EmployeeProfileTable FillDataSearchRecord(IDataRecord myDataRecord)
{
    EmployeeProfileTable myEmployeeProfileTable = new EmployeeProfileTable();
    if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("EmployeeProfileKey")))
    {
        myEmployeeProfileTable.EmployeeProfileKey = myDataRecord.GetInt32(myDataRecord.GetOrdinal("EmployeeProfileKey"));
    }
    if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("UserAccountKey")))
    {
        myEmployeeProfileTable.UserAccountKey = myDataRecord.GetInt32(myDataRecord.GetOrdinal("UserAccountKey"));
    }
    if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("FirstName")))
    {
        myEmployeeProfileTable.FirstName = myDataRecord.GetString(myDataRecord.GetOrdinal("FirstName"));
    }
    if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("LastName")))
    {
        myEmployeeProfileTable.LastName = myDataRecord.GetString(myDataRecord.GetOrdinal("LastName"));
    }
    if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("OtherName")))
    {
        myEmployeeProfileTable.OtherName = myDataRecord.GetString(myDataRecord.GetOrdinal("OtherName"));
    }

    if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("Gender")))
    {
        myEmployeeProfileTable.Gender = myDataRecord.GetString(myDataRecord.GetOrdinal("Gender"));
    }
    if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("DateOfBirth")))
    {
        myEmployeeProfileTable.DateOfBirth = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("DateOfBirth"));
    }
    if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("PlaceOfBirth")))
    {
        myEmployeeProfileTable.PlaceOfBirth = myDataRecord.GetString(myDataRecord.GetOrdinal("PlaceOfBirth"));
    }
    if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("JoinDate")))
    {
        myEmployeeProfileTable.JoinDate = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("JoinDate"));
    }
    if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("ParentStatus")))
    {
        myEmployeeProfileTable.ParentStatus = myDataRecord.GetInt16(myDataRecord.GetOrdinal("ParentStatus"));
    }
    if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("Hobby")))
    {
        myEmployeeProfileTable.Hobby = myDataRecord.GetString(myDataRecord.GetOrdinal("Hobby"));
    }
    if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("BloodGroupCode")))
    {
        myEmployeeProfileTable.BloodGroupCode = myDataRecord.GetInt16(myDataRecord.GetOrdinal("BloodGroupCode"));
    }
    if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("MartialStatusCode")))
    {
        myEmployeeProfileTable.MartialStatusCode = myDataRecord.GetInt16(myDataRecord.GetOrdinal("MartialStatusCode"));
    }
    if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("RaceCode")))
    {
        myEmployeeProfileTable.RaceCode = myDataRecord.GetInt16(myDataRecord.GetOrdinal("RaceCode"));
    }
    if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("ReligionCode")))
    {
        myEmployeeProfileTable.ReligionCode = myDataRecord.GetInt16(myDataRecord.GetOrdinal("ReligionCode"));
    }
    if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("NationalityCode")))
    {
        myEmployeeProfileTable.NationalityCode = myDataRecord.GetInt16(myDataRecord.GetOrdinal("NationalityCode"));
    }
    if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("NRICNumber")))
    {
        myEmployeeProfileTable.NRICNumber = myDataRecord.GetString(myDataRecord.GetOrdinal("NRICNumber"));
    }
    if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("DrivingLicenceType")))
    {
        myEmployeeProfileTable.DrivingLicenceType = myDataRecord.GetInt16(myDataRecord.GetOrdinal("DrivingLicenceType"));
    }
    if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("DrivingLicenseNumber")))
    {
        myEmployeeProfileTable.DrivingLicenseNumber = myDataRecord.GetString(myDataRecord.GetOrdinal("DrivingLicenseNumber"));
    }
    if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("TravelDocumentType")))
    {
        myEmployeeProfileTable.TravelDocumentType = myDataRecord.GetInt16(myDataRecord.GetOrdinal("TravelDocumentType"));
    }
    if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("TravelDocumentNumber")))
    {
        myEmployeeProfileTable.TravelDocumentNumber = myDataRecord.GetString(myDataRecord.GetOrdinal("TravelDocumentNumber"));
    }
    if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("WebSiteUrl")))
    {
        myEmployeeProfileTable.WebSiteUrl = myDataRecord.GetString(myDataRecord.GetOrdinal("WebSiteUrl"));
    }
    if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("Status")))
    {
        myEmployeeProfileTable.Status = myDataRecord.GetInt16(myDataRecord.GetOrdinal("Status"));
    }
    if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("CreatedDateTime")))
    {
        myEmployeeProfileTable.CreatedDateTime = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("CreatedDateTime"));
    }
    if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("ModifiedDateTime")))
    {
        myEmployeeProfileTable.ModifiedDateTime = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("ModifiedDateTime"));
    }

    if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("Employee")))
    {
        myEmployeeProfileTable.Employee = myDataRecord.GetString(myDataRecord.GetOrdinal("Employee"));
    }
    if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("SchoolKey")))
    {
        myEmployeeProfileTable.SchoolKey = myDataRecord.GetInt32(myDataRecord.GetOrdinal("SchoolKey"));
    }
    return myEmployeeProfileTable;
}
/// <summary>
/// Deletes a Employee ProfileTable from the database.
/// </summary>
/// <param name="schoolKey">The SchoolKey of the SchoolProfileTable to delete.</param>
/// <returns>Returns true when the object was deleted successfully, or false otherwise.</returns>
public static bool UpdatePhoto(int EmployeeProfileKey, Byte[] imgPhoto)
{
    int result = 0;
    using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
    {
        using (SqlCommand myCommand = new SqlCommand("sprocEmployeeProfileTableUpdatePhotoSingleItem", myConnection))
        {
            myCommand.CommandType = CommandType.StoredProcedure;

            myCommand.Parameters.AddWithValue("@EmployeeProfileKey", EmployeeProfileKey);
            myCommand.Parameters.AddWithValue("@Photo", imgPhoto);
            myConnection.Open();
            result = myCommand.ExecuteNonQuery();
            myConnection.Close();
        }
    }
    return result > 0;
}


/// <summary>
/// Get Photo a EmployeeProfileTable from the database.
/// </summary>
/// <param name="schoolKey">The SchoolKey of the SchoolProfileTable to delete.</param>
/// <returns>Returns true when the object was deleted successfully, or false otherwise.</returns>
public static Byte[] GetPhoto(int EmployeeProfileKey)
{
    Byte[] imgPhoto = null;
    using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
    {
        using (SqlCommand myCommand = new SqlCommand("sprocEmployeeProfileTableSelectPhotoSingleItem", myConnection))
        {
            myCommand.CommandType = CommandType.StoredProcedure;

            myCommand.Parameters.AddWithValue("@EmployeeProfileKey", EmployeeProfileKey);
            myConnection.Open();
            myCommand.CommandType = CommandType.StoredProcedure;

            using (SqlDataReader myReader = myCommand.ExecuteReader())
            {
                if (myReader.HasRows)
                {
                    while (myReader.Read())
                    {
                        imgPhoto = (Byte[])myReader["Photo"];
                    }
                }
                myReader.Close();
            }
            myConnection.Close();
        }
    }
    return imgPhoto;
}


}
